/*
 * MIT License
 *
 * Copyright (c) 2023 北京凯特伟业科技有限公司
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */
package com.je.common.base.service.db.impl;

import com.google.common.base.Strings;
import com.je.common.base.DynaBean;
import com.je.common.base.entity.QueryInfo;
import com.je.core.entity.extjs.JSONTreeNode;
import com.je.common.base.service.db.PcDBMethodService;
import org.springframework.stereotype.Service;

import java.util.List;

@Service("pcDBMethod4MySqlService")
public class PcDBMethod4MySqlServiceImpl implements PcDBMethodService {

    /**
     * 构建查询同步树的sql语句
     *
     * @param template  TODO 暂不明确
     * @param queryInfo TODO 暂不明确
     * @param tableName 表名称
     * @param rootId    根节点id
     * @return
     */
    @Override
    public String getTreeSql(JSONTreeNode template, QueryInfo queryInfo, String tableName, String rootId) {
        StringBuffer filedSql = new StringBuffer();
        filedSql.append("" + template.getId() + "," + template.getCode() + "," + template.getText() + "," + template.getParent());
        // 节点类型
        if (!Strings.isNullOrEmpty(template.getNodeType())) {
            filedSql.append("," + template.getNodeType());
        }
        // 节点信息
        if (!Strings.isNullOrEmpty(template.getNodeInfo())) {
            filedSql.append("," + template.getNodeInfo());
        }
        // 节点信息类型
        if (!Strings.isNullOrEmpty(template.getNodeInfoType())) {
            filedSql.append("," + template.getNodeInfoType());
        }
        if (!Strings.isNullOrEmpty(template.getLayer())) {
            filedSql.append("," + template.getLayer());
        }
        // 图标图片地址
        if (!Strings.isNullOrEmpty(template.getIcon())) {
            filedSql.append("," + template.getIcon());
        }
        // 图标颜色
        if (!Strings.isNullOrEmpty(template.getIconColor())) {
            filedSql.append("," + template.getIconColor());
        }
        //是否禁用
        if (!Strings.isNullOrEmpty(template.getDisabled())) {
            filedSql.append("," + template.getDisabled());
        }
        //树形路径
        if (!Strings.isNullOrEmpty(template.getNodePath())) {
            filedSql.append("," + template.getNodePath());
        }
        //描述
        if (!Strings.isNullOrEmpty(template.getDescription())) {
            filedSql.append("," + template.getDescription());
        }
        if (!Strings.isNullOrEmpty(template.getOrderIndex())) {
            filedSql.append("," + template.getOrderIndex());
        }
        if (!Strings.isNullOrEmpty(template.getTreeOrderIndex())) {
            filedSql.append("," + template.getTreeOrderIndex());
        }
        StringBuffer sql = new StringBuffer();
        sql.append(" select " + filedSql + " from " + tableName + " where 1=1 and (" + template.getNodePath() + " like '%" + rootId + "%'");
        if (null != queryInfo) {
            sql.append(queryInfo.getWhereSql());
        }
        sql.append(") OR " + template.getId() + " = '" + rootId + "' ");
        if (null != queryInfo && !Strings.isNullOrEmpty(queryInfo.getOrderSql())) {
            sql.append(queryInfo.getOrderSql());
        } else {
            sql.append(" ORDER BY " + template.getParent() + " asc");
            if (!Strings.isNullOrEmpty(template.getOrderIndex())) {
                sql.append(", " + template.getOrderIndex() + " asc ");
            }
        }
        return sql.toString();
    }

    /**
     * 构建查询异步树的sql语句
     *
     * @param template     TODO 暂不明确
     * @param queryInfo    TODO 暂不明确
     * @param tableName    表名称
     * @param rootId       根节点id
     * @param isRoot       TODO 暂不明确
     * @param onlyWhereSql TODO 暂不明确
     * @return
     */
    @Override
    public String getAsynTreeSql(JSONTreeNode template, QueryInfo queryInfo, String tableName, String rootId, Boolean isRoot, Boolean onlyWhereSql) {
        // TODO Auto-generated method stub
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT " + template.getId()); //主键
        sql.append(", " + template.getText()); // 名称
        sql.append("," + template.getCode()); // 编码
        sql.append("," + template.getParent()); // 父节点

        // 节点类型
        if (!Strings.isNullOrEmpty(template.getNodeType())) {
            sql.append("," + template.getNodeType());
        }
        // 节点信息
        if (!Strings.isNullOrEmpty(template.getNodeInfo())) {
            sql.append("," + template.getNodeInfo());
        }
        // 节点信息类型
        if (!Strings.isNullOrEmpty(template.getNodeInfoType())) {
            sql.append("," + template.getNodeInfoType());
        }
        // 图标图片地址
        if (!Strings.isNullOrEmpty(template.getIcon())) {
            sql.append("," + template.getIcon());
        }
        // 图标颜色
        if (!Strings.isNullOrEmpty(template.getIconColor())) {
            sql.append("," + template.getIconColor());
        }
        //是否禁用
        if (!Strings.isNullOrEmpty(template.getDisabled())) {
            sql.append("," + template.getDisabled());
        }
        //树形路径
        if (!Strings.isNullOrEmpty(template.getNodePath())) {
            sql.append("," + template.getNodePath());
        }
        //描述
        if (!Strings.isNullOrEmpty(template.getDescription())) {
            sql.append("," + template.getDescription());
        }
        if (!Strings.isNullOrEmpty(template.getOrderIndex())) {
            sql.append("," + template.getOrderIndex());
        }
        if (!Strings.isNullOrEmpty(template.getTreeOrderIndex())) {
            sql.append("," + template.getTreeOrderIndex());
        }

        sql.append(" FROM " + tableName + " t ");
        sql.append(" where 1=1 ");
        if (null != queryInfo) {
            sql.append(queryInfo.getWhereSql());
        }
        if (!onlyWhereSql) {
            if (isRoot) {
                sql.append(" AND (" + template.getParent() + "='" + rootId + "' OR " + template.getId() + "='" + rootId + "')");
            } else {
                sql.append("AND " + template.getParent() + "='" + rootId + "'");
            }
        }
//		sql.append(") OR "+template.getId()+"='"+rootId+"' ");
        if (null != queryInfo && !Strings.isNullOrEmpty(queryInfo.getOrderSql())) {
            sql.append(queryInfo.getOrderSql());
        } else {
            sql.append(" ORDER BY " + template.getParent() + " asc");
            if (!Strings.isNullOrEmpty(template.getOrderIndex())) {
                sql.append(", " + template.getOrderIndex() + " asc");
            }
        }
        return sql.toString();
    }

    /**
     * 查询树形的count
     *
     * @param template  TODO 暂不明确
     * @param queryInfo TODO 暂不明确
     * @param tableName 表名称
     * @param rootId    根节点id
     * @return
     */
    @Override
    public String getAsynTreeCount(JSONTreeNode template, QueryInfo queryInfo,
                                   String tableName, String rootId) {
        // TODO Auto-generated method stub
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(" + template.getId() + ""); // 0
        sql.append(") FROM " + tableName + " ");
        sql.append(" where 1=1 ");
        if (null != queryInfo) {
            sql.append(queryInfo.getWhereSql());
        }
        if (!Strings.isNullOrEmpty(rootId)) {
            sql.append(" and " + template.getParent() + " = '" + rootId + "'");
        } else {
            sql.append(" and " + template.getParent() + " is null ");
        }
        return sql.toString();
    }

    /**
     * 构建DynaBean查询同步树的sql语句
     *
     * @param columns
     * @param template  TODO 暂不明确
     * @param tableName 表名称
     * @param rootId    根节点id
     * @param queryInfo TODO 暂不明确
     * @return
     */
    @Override
    public String getDynaTreeSql(List<DynaBean> columns, JSONTreeNode template, String tableName, String rootId, QueryInfo queryInfo) {
        // TODO Auto-generated method stub
        StringBuffer filedSql = new StringBuffer();
        for (DynaBean column : columns) {
            filedSql.append(" " + column.getStr("TABLECOLUMN_CODE") + ",");
        }
        filedSql.deleteCharAt(filedSql.length() - 1);
        StringBuffer sql = new StringBuffer();
        sql.append(" select " + filedSql.toString() + " from " + tableName + " where 1=1 and (" + template.getNodePath() + " like '%" + rootId + "%'");
        if (null != queryInfo) {
            sql.append(queryInfo.getWhereSql());
        }
        sql.append(") OR " + template.getId() + " = '" + rootId + "' ");
        if (null != queryInfo && !Strings.isNullOrEmpty(queryInfo.getOrderSql())) {
            sql.append(queryInfo.getOrderSql());
        } else {
            sql.append(" ORDER BY " + template.getParent() + " asc");
            if (!Strings.isNullOrEmpty(template.getOrderIndex())) {
                sql.append(", " + template.getOrderIndex() + " asc ");
            }
        }
        return sql.toString();
    }

    /**
     * 获取角色权限查询sql
     *
     * @param rootId 根节点
     * @return
     */
    @Override
    public String getRolePermSql(String rootId) {
        // TODO Auto-generated method stub
        //PCServiceTemplate pcServiceTemplate=SpringContextHolder.getBean("PCServiceTemplateImpl");
//		pcServiceTemplate.executeSql(" call p_opt_recursion('JE_CORE_ROLE','ROLEID','PARENT','"+rootId+"','xxx_table')");
        StringBuffer filedSql = new StringBuffer();
        filedSql.append("ROLEID,ROLENAME,ROLECODE,GROUPNAME,GROUPCODE,EXTENDGROUPNAME,EXTENDGROUPCODE,REJECTGROUPNAME,REJECTGROUPCODE,PATH,PARENT,ORDERINDEX");
        StringBuffer sql = new StringBuffer();
        sql.append(" select " + filedSql.toString() + " from JE_CORE_ROLE where 1=1 and (PATH LIKE '%" + rootId + "%' OR ROLEID='" + rootId + "')");
        sql.append(" ORDER BY PARENT ASC,ORDERINDEX ASC");
        return sql.toString();
    }

    /**
     * 获取角色权限查询sql
     *
     * @param rootId 根节点
     * @return
     */
    @Override
    public String getRolePermLikeSql(String rootId) {
        // TODO Auto-generated method stub
        StringBuffer filedSql = new StringBuffer();
        filedSql.append("ROLEID,ROLENAME,ROLECODE,GROUPNAME,GROUPCODE,EXTENDGROUPNAME,EXTENDGROUPCODE,REJECTGROUPNAME,REJECTGROUPCODE,PATH,PARENT,ORDERINDEX");
        StringBuffer sql = new StringBuffer();
        sql.append(" select " + filedSql.toString() + " from JE_CORE_ROLE where 1=1 and (PATH LIKE '%" + rootId + "%' OR ROLEID='" + rootId + "')");
        sql.append(" ORDER BY PARENT ASC,ORDERINDEX ASC");
        return sql.toString();
    }

    /**
     * 获取角色组查询SQL
     *
     * @param rootId 根节点
     * @return
     */
    @Override
    public String getRoleGroupPermSql(String rootId) {
        // TODO Auto-generated method stub
//		PCServiceTemplate pcServiceTemplate=SpringContextHolder.getBean("PCServiceTemplateImpl");
//		pcServiceTemplate.executeSql(" call p_opt_recursion('JE_CORE_ROLEGROUP','JE_CORE_ROLEGROUP_ID','SY_PARENT','"+rootId+"','xxx_table')");
        StringBuffer filedSql = new StringBuffer();
        filedSql.append("JE_CORE_ROLEGROUP_ID,ROLEGROUP_TEXT,ROLEGROUP_CODE,SY_PATH,SY_PARENT,SY_ORDERINDEX");
        StringBuffer sql = new StringBuffer();
        sql.append(" select " + filedSql.toString() + " from JE_CORE_ROLEGROUP where 1=1 and (SY_PATH LIKE '%" + rootId + "%' OR JE_CORE_ROLEGROUP_ID ='" + rootId + "')");
        sql.append(" ORDER BY SY_PARENT ASC,SY_PARENT ASC");
        return sql.toString();
    }

    /**
     * 获取角色组查询SQL
     *
     * @param rootId 根节点
     * @return
     */
    @Override
    public String getRoleGroupPermLikeSql(String rootId) {
        // TODO Auto-generated method stub
        StringBuffer filedSql = new StringBuffer();
        filedSql.append("JE_CORE_ROLEGROUP_ID,ROLEGROUP_TEXT,ROLEGROUP_CODE,SY_PATH,SY_PARENT,SY_ORDERINDEX");
        StringBuffer sql = new StringBuffer();
        sql.append(" select " + filedSql.toString() + " from JE_CORE_ROLEGROUP where 1=1 and (SY_PATH LIKE '%" + rootId + "%' OR JE_CORE_ROLEGROUP_ID ='" + rootId + "')");
        sql.append(" ORDER BY SY_PARENT ASC,SY_PARENT ASC");
        return sql.toString();
    }

    /**
     * 获取数据生成UUID函数
     *
     * @return
     */
    @Override
    public String getGenerateUUID() {
        return "uuid()";
    }

    /**
     * 获取截取字符串函数
     *
     * @return
     */
    @Override
    public String getSubString() {
        return "substring";
    }

    /**
     * 获取字符串长度函数
     *
     * @return
     */
    @Override
    public String getLength() {
        return "length";
    }

    /**
     * 获取数据库修改视图语句
     *
     * @return
     */
    @Override
    public String getUpdateView() {
        return "CREATE OR REPLACE VIEW ";
    }

}
